/**
 * Copyright(C) 2012 Luvina Software Company
 *
 * TblUserDaoImpl.java, 28/12/2012, NgocAnh
 */
package net.luvina.manageuser.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import net.luvina.manageuser.dao.TblUserDao;
import net.luvina.manageuser.dto.UserDetailDto;
import net.luvina.manageuser.hibernate.TblUser;

/**
 * TblUserDaoImpl implement TblUserDao
 *
 * @author la-pm Ngoc Anh
 *
 */
@Repository("TblUserDao")
public class TblUserDaoImpl extends GenericDaoImpl<TblUser, Integer> implements
        TblUserDao {

    public TblUserDaoImpl() {
        super(TblUser.class);
    }

    /*
     * (non-Javadoc)
     *
     * @see net.luvina.manageuser.dao.TblUserDao#getListUser(int, int, int,
     * java.lang.String, java.lang.String, java.lang.String, java.lang.String,
     * java.lang.String)
     */
    @SuppressWarnings("unchecked")
    public List<UserDetailDto> getListUser(final int offset, final int limit,
            final int groupId, final String fullName, final String sortType,
            final String sortByFullName, final String sortByCodeLevel,
            final String sortByEndDate) {
        HibernateCallback callback = new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException, SQLException {
                StringBuilder sqlCommand = new StringBuilder("");
                sqlCommand = sqlCommand.append(" SELECT  ");
                sqlCommand = sqlCommand.append(" u.user_id as userId, ");
                sqlCommand = sqlCommand.append(" u.full_name as fullName, ");
                sqlCommand = sqlCommand.append(" u.birthday as birthday, ");
                sqlCommand = sqlCommand.append(" u.email as email, ");
                sqlCommand = sqlCommand.append(" u.tel as tel, ");

                sqlCommand = sqlCommand.append(" g.group_name as groupName, ");

                sqlCommand = sqlCommand.append(" j.name_level as nameLevel, ");
                sqlCommand = sqlCommand.append(" tduj.end_date as endDate, ");
                sqlCommand = sqlCommand.append(" tduj.total as total ");

                sqlCommand = sqlCommand.append(" FROM tbl_user AS u ");
                sqlCommand = sqlCommand.append(" LEFT JOIN mst_group AS g ");
                sqlCommand = sqlCommand.append(" ON u.group_id = g.group_id ");

                sqlCommand = sqlCommand
                        .append(" LEFT JOIN tbl_detail_user_japan AS tduj ");
                sqlCommand = sqlCommand.append(" ON u.user_id = tduj.user_id ");

                sqlCommand = sqlCommand.append(" LEFT JOIN mst_japan AS j ");
                sqlCommand = sqlCommand
                        .append(" ON tduj.code_level = j.code_level ");

                if (groupId > 0 && (fullName != null && !"".equals(fullName))) {
                    sqlCommand.append(" WHERE g.group_id=:groupId");
                    sqlCommand.append(" AND u.full_name LIKE :fullName");
                } else {
                    if (fullName != null && !"".equals(fullName)) {
                        sqlCommand.append(" WHERE u.full_name LIKE :fullName");
                    }
                    if (groupId > 0) {
                        sqlCommand.append(" WHERE g.group_id=:groupId");
                    }
                }

                if ("full_name".equals(sortType)) {
                    sqlCommand
                            .append(" ORDER BY u.full_name " + sortByFullName);
                    sqlCommand.append(" ,tduj.code_level " + sortByCodeLevel);
                    sqlCommand.append(" ,tduj.end_date " + sortByEndDate);

                } else if ("code_level".equals(sortType)) {
                    sqlCommand.append(" ORDER BY tduj.code_level "
                            + sortByCodeLevel);
                    sqlCommand.append(" ,u.full_name " + sortByFullName);
                    sqlCommand.append(" ,tduj.end_date " + sortByEndDate);

                } else if ("end_date".equals(sortType)) {
                    sqlCommand.append(" ORDER BY tduj.end_date "
                            + sortByEndDate);
                    sqlCommand.append(" ,u.full_name " + sortByFullName);
                    sqlCommand.append(" ,tduj.code_level " + sortByCodeLevel);
                } else {
                    sqlCommand
                            .append(" ORDER BY u.full_name " + sortByFullName);
                    sqlCommand.append(" ,tduj.code_level " + sortByCodeLevel);
                    sqlCommand.append(" ,tduj.end_date " + sortByEndDate);
                }

                Query query = session.createSQLQuery(sqlCommand.toString())
                        .addScalar("userId", Hibernate.INTEGER).addScalar(
                                "fullName", Hibernate.STRING).addScalar(
                                "birthday", Hibernate.DATE).addScalar("email",
                                Hibernate.STRING).addScalar("tel",
                                Hibernate.STRING).addScalar("groupName",
                                Hibernate.STRING).addScalar("nameLevel",
                                Hibernate.STRING).addScalar("endDate",
                                Hibernate.DATE).addScalar("total",
                                Hibernate.INTEGER).setResultTransformer(
                                Transformers.aliasToBean(UserDetailDto.class));

                if (groupId > 0 && (fullName != null && !"".equals(fullName))) {
                    query.setString("groupId", String.valueOf(groupId));
                    query.setString("fullName", "%" + fullName + "%");
                } else {
                    if (fullName != null && !"".equals(fullName)) {
                        query.setString("fullName", "%" + fullName + "%");
                    }
                    if (groupId > 0) {
                        query.setString("groupId", String.valueOf(groupId));
                    }
                }

                 /*query.setFirstResult(offset);
                 query.setMaxResults(limit);
*/
                return (List<UserDetailDto>) query.list();

            }
        };
        return (List<UserDetailDto>) getHibernateTemplate().execute(callback);
    }

    /*
     * (non-Javadoc)
     *
     * @see net.luvina.manageuser.dao.TblUserDao#getTotalRecords(int,
     * java.lang.String)
     */
    public int getTotalRecords(final int groupId, final String fullName) {
        HibernateCallback callback = new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException {
                StringBuilder sqlBuilder = new StringBuilder();
                sqlBuilder.append(" FROM TblUser u ");
                sqlBuilder.append(" INNER JOIN FETCH u.mstGroup g ");
                if (groupId > 0 && (fullName != null && !"".equals(fullName))) {
                    sqlBuilder.append(" WHERE g.groupId=:groupId");
                    sqlBuilder.append(" AND u.fullName LIKE :fullName");
                } else {
                    if (fullName != null && !"".equals(fullName)) {
                        sqlBuilder.append(" WHERE u.fullName LIKE :fullName");
                    }
                    if (groupId > 0) {
                        sqlBuilder.append(" WHERE g.groupId=:groupId");
                    }
                }
                Query query = session.createQuery(sqlBuilder.toString());

                if (groupId > 0 && (fullName != null && !"".equals(fullName))) {
                    query.setString("groupId", String.valueOf(groupId));
                    query.setString("fullName", "%" + fullName + "%");
                } else {
                    if (fullName != null && !"".equals(fullName)) {
                        query.setString("fullName", "%" + fullName + "%");
                    }
                    if (groupId > 0) {
                        query.setString("groupId", String.valueOf(groupId));
                    }
                }
                List<TblUser> userList = query.list();
                if (userList != null && userList.size() > 0) {
                    return userList.size();
                } else {
                    return 0;
                }
            }
        };
        return (Integer) getHibernateTemplate().execute(callback);
    }
}
